CREATE     procedure amsp_ICPromote
    @InSourceNodeID    numeric
AS
BEGIN

  /*
  ** DESCRIPTION:
  ** ------------
  ** Promotes a node in the Interest_Category tree (InSourceNodeID) to be the last sibling of its parent node.
  **
  ** INPUTS:
  ** -------
  **   @InSourceNodeID    Source node being promoted
  **
  ** OUTPUTS:
  ** -----------
  **   none
  **
  ** NOTES:
  ** ------
  **   none
  **
  ** HISTORY:
  ** --------
  **   04/23/2003    N.Malhotra    Initial Version Created
  **
  */

  Declare  
    @DestID            numeric,
    @SourceSortOrder        numeric,
    @SourceDepth        numeric,
    @SourceParentID        numeric


  BEGIN TRANSACTION

  /*
  ** Let's find out about our source node
  */

  SELECT
    @SourceSortOrder  = SortOrder,
    @SourceDepth      = CategoryDepth,
    @SourceParentID   = ParentCategoryID
  FROM
    Interest_Category
  WHERE
    InterestCategoryID = @InSourceNodeID

  /*
  ** Can't do anything if it is already a level 1
  */

  IF @SourceDepth > 1 BEGIN

    /*
    ** Get the parent's parent node (this will be the new parent)
    */

    SELECT @DestID = ParentCategoryID
      FROM Interest_Category
     WHERE InterestCategoryID = @SourceParentID

    IF @SourceDepth = 2 BEGIN

      /*
      ** Promoting to a level 1 is a special case scenario
      */

      UPDATE Interest_Category
         SET ParentCategoryID = NULL,
             AncestorCategoryID = InterestCategoryID,
             CategoryDepth = 1
       WHERE InterestCategoryID = @InSourceNodeID

    END
    ELSE BEGIN

      UPDATE Interest_Category
         SET ParentCategoryID = @DestID
       WHERE InterestCategoryID = @InSourceNodeID

    END


    /*
    ** amsp_FixTree will set the AncestorID, SortOrder, CategoryDepth for the entire tree.
    */
    
    EXEC amsp_ICFixTree


    COMMIT TRANSACTION

  END

END

GO
GRANT EXECUTE ON  [dbo].[amsp_ICPromote] TO [IMIS]
GO